#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use utf8;
use Encode;

my $fichier=$ARGV[0];
my $exp=encode_utf8($ARGV[1]);
$exp=~s/_/\ /;
my $label=encode_utf8($ARGV[2]);
$label=~s/_/\ /;
my $unite=encode_utf8($ARGV[3]);
$unite=~s/_/\ /;
my $desc='';
if($ARGV[4]){
	$desc=encode_utf8($ARGV[4]);
	$desc=~s/_/\ /;
}
# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});

# ************************************* Création type_test ************************************* #
my $id_type_test;
if($desc ne ''){
	$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%$label%' AND description LIKE '%$desc%' AND unite_mesure LIKE '%$unite%'");

	if(not $id_type_test){
		$dbh->do("INSERT INTO Type_test(label,description,unite_mesure) VALUES ('$label','$desc','$unite')"); 
		$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%$label%' AND unite_mesure LIKE '%$unite%'");
	}
}
else{
	$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%$label%' AND unite_mesure LIKE '%$unite%'");

	if(not $id_type_test){
		$dbh->do("INSERT INTO Type_test(label,unite_mesure) VALUES ('$label','$unite')"); 
		$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%$label%' AND unite_mesure LIKE '%$unite%'");
	}
}

my $worksheet;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

foreach($workbook->worksheets()){
    $worksheet=$_;
}

my ($row_min,$row_max) = $worksheet->row_range();
my ($col_min, $col_max) = $worksheet->col_range();

for my $row ($row_min .. $row_max){ #parcours lignes
	my $code;
	my $date;
	my $valeur;
	my $description;
	
	for my $col ($col_min .. $col_max){ #parcours colonnes
		if($col eq 0){
			my $cont=$worksheet->get_cell($row,$col);
			if($cont and $cont->value()=~/\d\d\d\d-\d\d-\d\d/){
				$date=$cont->value();
				my $cont2=$worksheet->get_cell($row,1);
				$code=$cont2->value();
				my $cont3=$worksheet->get_cell($row,2);
				$valeur=$cont3->value();
				my $cont4=$worksheet->get_cell($row,3);
				if($cont4){
					$description=$cont4->value();
				}
			}
		}
	}
	
	if($date){
		
		# ************************************* Création animal ************************************* #
		my $id_anim;
		$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code')");
		
		if(not $id_anim){
			$dbh->do("INSERT INTO Animal(code_animal) VALUES ('$code')");
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM animal WHERE code_animal=(E'$code')");
		}
		
		# ************************************* Création test ************************************* #
		my $id_test2;
		### verification que le test n'existe pas deja ###
		$id_test2=$dbh->selectrow_array("SELECT id_test FROM Test WHERE experimentateur='$exp' AND id_type_test=$id_type_test AND date_test=(E'$date')");
				
		### ajout du test ###
		if(not $id_test2){
			$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$date', '$exp', $id_type_test)");
			$id_test2=$dbh->selectrow_array("SELECT id_test FROM Test WHERE experimentateur='$exp' AND id_type_test=$id_type_test AND date_test=(E'$date')");
		}

		# ************************************* Création resultat_test ************************************* #
		my $id_res2;
		$id_res2=$dbh->selectrow_array("SELECT id_res FROM resultat_test WHERE id_test=$id_test2 AND id_animal=$id_anim");
		
		if(not $id_res2){
			$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test2, $id_anim)");
			$id_res2=$dbh->selectrow_array("SELECT id_res from resultat_test WHERE id_test=$id_test2 AND id_animal=$id_anim");
		}
		
		# ************************************* Création resultat_test_simple ************************************* #
			
		my $resultat=$dbh->selectrow_array("SELECT id_res FROM resultat_test_simple WHERE id_res=$id_res2");
		
		if(not $resultat){
			if(not $description){
				$dbh->do("INSERT INTO Resultat_test_simple(id_res, mesure) VALUES($id_res2, '$valeur')");			
			}
			else{
				my $temp = encode_utf8($description);
				$dbh->do("INSERT INTO Resultat_test_simple(id_res, mesure,description) VALUES($id_res2, '$valeur','$temp')");		
			}	
		}
	}
}
